IF OBJECT_ID('dbo.RptSubContractWrapperDetailReport') IS NOT NULL
BEGIN
    DROP PROCEDURE dbo.RptSubContractWrapperDetailReport
    IF OBJECT_ID('dbo.RptSubContractWrapperDetailReport') IS NOT NULL
        PRINT '<<< FAILED DROPPING PROCEDURE dbo.RptSubContractWrapperDetailReport >>>'
    ELSE
        PRINT '<<< DROPPED PROCEDURE dbo.RptSubContractWrapperDetailReport >>>'
END
go
-- =======================================================================
-- Procedure Name: dbo.RptSubContractWrapperDetailReport
--
-- Author:      Kevin Cashman, Parallax Consulting LLC
--
-- Create date: 10/06/2008
--
-- Description: Gets Raw data of SubDeliverables, based on type of Report.
--
-- Serves as a drill down to the Report, RptSubContractWrapperReport
--
-- Parameters:  
--     @StartDate                         
--     @EndDate                           
--     @DeliverableStatus                 
--     @Institution                       
--     @Department                        
--     @Unit                              
--     @Subcontractor                     
--     @SubcontractSpecialist             
--     @DomainUserId                      
--
--
-- Returns:     INT, 0=Success, -1=Error
--
-- SampleCall:
-- EXECUTE dbo.RptSubContractWrapperDetailReport(
--                       @StartDate               = '6/1/2008',
--                       @EndDate                 = '8/31/2008',
--                       @DeliverableStatus       = NULL, 
--                       @Institution             = NULL, 
--                       @Department              = NULL, 
--                       @Unit                    = NULL, 
--                       @Subcontractor           = NULL, 
--                       @SubcontractSpecialist   = NULL,
--                       @ReportType              = 'SubInActivity' (or 'SubOutActivity', 'SubDeliverables')
--                       @DomainUserId            = 11055 )
--
--------------------------------------------------------------------------
-- Date        Initials  Modification
--------------------------------------------------------------------------
-- 10/06/08    KC        Created
-- 10/09/08    KC        Removed DeliverableType as a Param.
-- 10/10/08    KC        Added ChiefCode as a Param
-- 11/13/08    KC        Made sure DeliverableType is passed
-- 12/08/08    KC        Added @FundNumber and @PrincipalInvestigator
-- 12/09/08    KC        Added @OriginatingSponsor and @Sponsor
-- 12/10/08    KC        Added WITH RECOMPILE
--
-- =======================================================================
CREATE PROCEDURE dbo.RptSubContractWrapperDetailReport
     (
     @StartDate                         DATETIME,  
     @EndDate                           DATETIME,
     @DeliverableType                   VARCHAR(100) = NULL,
     @DeliverableStatus                 VARCHAR(100) = NULL,
     @Institution                       VARCHAR(100) = NULL,
     @Department                        VARCHAR(100) = NULL,
     @Unit                              VARCHAR(100) = NULL,
     @ChiefCode                         VARCHAR(4)   = NULL,
     @FundNumber                        VARCHAR(1000) = NULL,
     @PrincipalInvestigator             VARCHAR(100) = NULL, 
     @OriginatingSponsor                VARCHAR(100) = NULL,
     @Sponsor                           VARCHAR(100) = NULL,
     @Subcontractor                     VARCHAR(200) = NULL,
     @SubcontractSpecialist             VARCHAR(100) = NULL,
     @ReportType                        VARCHAR(25) = NULL,
     @ContractManager					VARCHAR(100) = NULL,
     @DomainUserId                      INT
     ) 
WITH RECOMPILE                                   
AS
BEGIN
     -- Initial Settings
     SET NOCOUNT ON
     
     -- Declare Local Variables
     DECLARE   @Error              INT,
               @RowCount           INT,
               @ReturnCode         INT

     IF @ReportType NOT IN ('SubInActivity','SubOutActivity','SubDeliverables') OR @ReportType IS NULL
     BEGIN
          RAISERROR ('ERROR! ReportType Parameter should be "SubInActivity" or "SubOutActivity" or "SubDeliverables", not "%s".',11,1,@ReportType) 
          RETURN -1
     END

     --  Which Report?
     IF @ReportType = 'SubInActivity'
     BEGIN
          EXECUTE @ReturnCode = dbo.RptSubContractInActivityDetailReport
                                   @StartDate               = @StartDate,
                                   @EndDate                 = @EndDate,
                                   @DeliverableStatus       = @DeliverableStatus,
                                   @Institution             = @Institution,
                                   @Department              = @Department,
                                   @Unit                    = @Unit,
                                   @ChiefCode               = @ChiefCode,
                                   @FundNumber              = @FundNumber,
                                   @PrincipalInvestigator   = @PrincipalInvestigator, 
                                   @OriginatingSponsor      = @OriginatingSponsor,
                                   @Sponsor                 = @Sponsor,
                                   @Subcontractor           = @Subcontractor,
                                   @SubcontractSpecialist   = @SubcontractSpecialist,
                                   @DomainUserId            = @DomainUserId

          -- check for errors
          SELECT @Error=@@ERROR
          IF @Error != 0 OR @ReturnCode != 0
          BEGIN
               RETURN -1
          END
     END
     
     --  Which Report?
     IF @ReportType = 'SubOutActivity'
     BEGIN
          EXECUTE @ReturnCode = dbo.RptSubContractOutActivityDetailReport
                                   @StartDate               = @StartDate,
                                   @EndDate                 = @EndDate,
                                   @DeliverableStatus       = @DeliverableStatus,
                                   @Institution             = @Institution,
                                   @Department              = @Department,
                                   @Unit                    = @Unit,
                                   @ChiefCode               = @ChiefCode,
                                   @FundNumber              = @FundNumber,
                                   @PrincipalInvestigator   = @PrincipalInvestigator, 
                                   @OriginatingSponsor      = @OriginatingSponsor,
                                   @Sponsor                 = @Sponsor,
                                   @Subcontractor           = @Subcontractor,
                                   @SubcontractSpecialist   = @SubcontractSpecialist,
                                   @DomainUserId            = @DomainUserId

          -- check for errors
          SELECT @Error=@@ERROR
          IF @Error != 0 OR @ReturnCode != 0
          BEGIN
               RETURN -1
          END
     END

     --  Which Report?
     IF @ReportType = 'SubDeliverables'
     BEGIN
          EXECUTE @ReturnCode = dbo.RptSubContractDeliverableDetailReport
                                   @StartDate               = @StartDate,
                                   @EndDate                 = @EndDate,
                                   @DeliverableType         = @DeliverableType,
                                   @DeliverableStatus       = @DeliverableStatus,
                                   @Institution             = @Institution,
                                   @Department              = @Department,
                                   @Unit                    = @Unit,
                                   @ChiefCode               = @ChiefCode,
                                   @FundNumber              = @FundNumber,
                                   @PrincipalInvestigator   = @PrincipalInvestigator, 
                                   @OriginatingSponsor      = @OriginatingSponsor,
                                   @Sponsor                 = @Sponsor,
                                   @Subcontractor           = @Subcontractor,
                                   @SubcontractSpecialist   = @SubcontractSpecialist,
								   @ContractManager			= @ContractManager,
                                   @DomainUserId            = @DomainUserId

          -- check for errors
          SELECT @Error=@@ERROR
          IF @Error != 0 OR @ReturnCode != 0
          BEGIN
               RETURN -1
          END
     END

     -- Return Success
     RETURN 0

     
END
go

IF OBJECT_ID('dbo.RptSubContractWrapperDetailReport') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE dbo.RptSubContractWrapperDetailReport >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE dbo.RptSubContractWrapperDetailReport >>>'
go

GRANT EXECUTE ON dbo.RptSubContractWrapperDetailReport TO InsightReport,Insight
go


